package rubix.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class PoManager {

    private static final String TBLNAME = "po";

    private static void handleSQLException(SQLException ex, String sql, String... parameters) {
        String msg = "Unable to access data; SQL=" + sql + "\n";

        for (String parameter : parameters) {
            msg += "," + parameter;
        }

        Logger.getLogger(PoManager.class.getName()).log(Level.SEVERE, msg, ex);
        throw new RuntimeException(msg, ex);
    }
    //method
    public static void prepareTables(){
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        try {
            conn = ConnectionManager.getConnection();            
            
            sql = "DROP TABLE IF EXISTS po";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            
            sql = "CREATE TABLE IF NOT EXISTS po("
                    + "poid VARCHAR(8) NOT NULL,"
                    + "supplierid VARCHAR(20) NOT NULL,"
                    + "purchasedate VARCHAR(8) NOT NULL,"
                    + "currency VARCHAR(3) NOT NULL,"                    
                    + "itemid VARCHAR(7) NOT NULL,"     
                    + "partid VARCHAR(50) NOT NULL,"     
                    + "partprice VARCHAR(10) NOT NULL,"
                    + "onorderquantity INTEGER(3) NOT NULL,"     					
                    + "PRIMARY KEY (poid)"
                    + ")";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "prepareTables");
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static void addPo(Po po) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        
        String poId = po.getPoId();
        String supplierId = po.getSupplierId();
        String purchaseDate = po.getPurchaseDate();
        String currency = po.getCurrency();
        String itemId = po.getItemId();
        String partId = po.getPartId();
        String partPrice = po.getPartPrice();
        int onOrderQuantity = po.getOnOrderQuantity();
        
        try {
            conn = ConnectionManager.getConnection();
            
            sql = "INSERT INTO " + TBLNAME + " (poid,supplierid,purchasedate,currency,itemid,partid,partprice,onorderquantity) VALUES (?,?,?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, poId);
            stmt.setString(2, supplierId);
            stmt.setString(3, purchaseDate);
            stmt.setString(4, currency);
            stmt.setString(5, itemId);
            stmt.setString(6, partId);
            stmt.setString(7,partPrice);
            stmt.setInt(8, onOrderQuantity);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId + ",supplierid=" + supplierId + ",purchasedate=" + purchaseDate + ",currency=" + currency + 
                ",itemid=" + itemId + ",partid=" + partId + ",onorderquantity=" + onOrderQuantity);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
    
    public static void deletePo(String poId) { 
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set onorderquantity = ? where poid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, 0);
            stmt.setString(2, poId);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId + ",onorderquantity=" + 0);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }    
    
    public static ArrayList<Po> retrieveAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        ArrayList<Po> results = new ArrayList<Po>();

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT poid,supplierid,purchasedate,currency,itemid,partid,partprice,onorderquantity FROM " + TBLNAME;
            stmt = conn.prepareStatement(sql);

            rs = stmt.executeQuery();
            while (rs.next()) {
                String poId = rs.getString("poid");
                String supplierId = rs.getString("supplierid");
                String purchaseDate = rs.getString("purchasedate");
                String currency = rs.getString("currency");
                String itemId = rs.getString("itemid");
                String partId = rs.getString("partid");
                String partPrice = rs.getString("partprice");
                int onOrderQuantity = rs.getInt("onorderquantity");

                results.add(new Po(poId,supplierId,purchaseDate,currency,itemId,partId,partPrice,onOrderQuantity));
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return results;
    }

    public static Po retrievePo(String poId) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "";
        Po result = null;

        try {
            conn = ConnectionManager.getConnection();

            sql = "SELECT supplierid,purchasedate,currency,itemid,partid,partprice,onorderquantity FROM " + TBLNAME + " WHERE poid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, poId);

            rs = stmt.executeQuery();

            while (rs.next()) {
                String supplierId = rs.getString("supplierid");
                String purchaseDate = rs.getString("purchasedate");
                String currency = rs.getString("currency");
                String itemId = rs.getString("itemid");
                String partId = rs.getString("partid");
                String partPrice = rs.getString("partprice");
                int onOrderQuantity = rs.getInt("onorderquantity");

                result = new Po(poId,supplierId,purchaseDate,currency,itemId,partId,partPrice,onOrderQuantity);
            }
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId);
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        return result;
    }
    
    public static void updateOnOrderQuantity(String poId, int onOrderQuantity) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        try {
            conn = ConnectionManager.getConnection();

            sql = "UPDATE " + TBLNAME + " set onorderquantity=? where poid = ?";
            stmt = conn.prepareStatement(sql);

            stmt.setInt(1, onOrderQuantity);
            stmt.setString(2, poId);

            stmt.executeUpdate();
        } catch (SQLException ex) {
            handleSQLException(ex, sql, "poid=" + poId + ",onorderquantity=" + onOrderQuantity);
        } finally {
            ConnectionManager.close(conn, stmt);
        }
    }
}